package com.example.lin.generate;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.example.lin.util.DateUtils;
import com.example.lin.util.ExportUtil;
import com.example.lin.util.ImportUtil;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.Test;

import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @className: generateSQLOutText
 * @description:
 * @author: Lin.Gu
 * @date: 2024/8/12
 */
public class GenerateSQLOutText {

    public static Map<String, String> serviceMpas = new HashMap<>();

    static {
        serviceMpas.put("ES", "65846172085915648");
        serviceMpas.put("EE", "65827813466312704");
    }


    @Test
    public void generateSQLPrint() {
        String filePath = "C:\\Users\\181596\\Desktop\\37个订单补充.xlsx";
        int sheet = 0;
//        String template = "INSERT INTO t_product_price_type (id, charges_id, charges, charges_en, charge_type, is_freeze, level1_subjects_id, level2_subjects_id, level3_subjects_id, project_code, markup_ratio, is_recount, is_enabled, is_active, create_time, create_user_code, modify_time, modify_user_code, is_invoice, invoice_type_code, is_cost_flow, taxation)\n" +
//                "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', 'eBay', '%s', '%s', 1, 1, now(), '181596', now(), '181596', '%s', '%s', 1, '%s');";
        List<List<Object>> list = ImportUtil.readExcelToList(filePath, sheet);
        //System.out.println(list);
        list.forEach(e -> {

            JSONObject bill = new JSONObject();
            bill.put("cashbackType", 2);
            bill.put("rebateBillNumber", e.get(5));
            JSONArray array = new JSONArray();
            for (String s : e.get(1).toString().split(",")) {
                JSONObject billDetail = new JSONObject();
                billDetail.put("trackingNumber", s);
                billDetail.put("orderCreatedTime", e.get(2));
                billDetail.put("deliveryTime", e.get(3));
                array.add(billDetail);
            }
            bill.put("saveOrder", array);
            System.out.println(bill);
//            try {
//                Thread.sleep(100);
//            } catch (Exception exception) {
//                exception.printStackTrace();
//            }
//            String id = System.currentTimeMillis() + "";
//            if (!e.get(0).equals("新增")) {
//                return;
//            }
//            String format = String.format(template, id, e.get(1), e.get(2), e.get(3), e.get(4), e.get(5), e.get(6), e.get(7), e.get(8),
//                    e.get(10), e.get(11), e.get(18), e.get(22), e.get(21));
//            System.out.println(format);
        });
    }

    @Test
    public void generateSQLOutText() {
        String filePath = "C:\\Users\\181596\\Desktop\\美国一般进口&加征税率-JP-20250908去钢铝铜加征50%版.xlsx";
        int sheet = 0;
        String template = "INSERT INTO `t_coo_manual_whitelist` (`id`, `coo_type`, `code_value`, `name_value`, `origin_country`, `destination_country`, `is_active`, `create_time`, `create_user_code`, `modify_time`, `modify_user_code`) " +
                "VALUES (%s, 'COO_HS_CODE_CATEGORY', \"%s\",NULL, 'JP', 'US', 1, NOW(), '181596', NOW(), '181596');";
        List<List<Object>> list = ImportUtil.readExcelToList(filePath, sheet);
        //System.out.println(list);
        List<Object> sqlList = new LinkedList<>();
        AtomicInteger i = new AtomicInteger();
        list.forEach(e -> {
            int andIncrement = i.getAndIncrement();
            String id = DateUtils.format(new Date(), DateUtils.YMDHS_FORMAT) + String.format("%06d", andIncrement);
            String codeValue = String.valueOf(e.get(0)).replaceAll("\\s+", "").toUpperCase();
            if(StringUtils.isBlank(codeValue)){
                return;
            }
            //String nameValue = String.valueOf(e.get(1)).replaceAll("\\s+", "").toUpperCase();
            String format = String.format(template, id, codeValue);
            sqlList.add(format);
        });
        ExportUtil.writeDataToFile(sqlList, "hscode代码CN" + sheet);
        System.out.println("完成");
    }


    /**
     * epass数据重call
     * 入参列表: 订单号
     */
    @Test
    public void generateHsCodeSQLOutTxt() {
        String filePath = "C:\\Users\\181596\\Desktop\\重call.xlsx";
        int sheet = 0;
        String template = "INSERT INTO `t_ccei_related_failure` (`id`, `business_code`, `business_name`, `send_status`, `send_times`, `failed_reason`, `retry_time`, `is_active`, `create_time`, `create_user_code`, `modify_time`, `modify_user_code`) \n" +
                "VALUES (%s, '%s', 'config-ccei-predeclared-db', 0, 0, '铜及衍生品重call', '%s', 1, NOW(), '181596', NOW(), '181596');";

        List<List<Object>> list = ImportUtil.readExcelToList(filePath, sheet);
        List<Object> sqlList = new LinkedList<>();
        AtomicInteger i = new AtomicInteger(1);
        final String[] retryTime = {DateUtils.format(new Date(), DateUtils.DEFAULT_DATETIME_FORMAT)};
        int idlen = String.valueOf(list.size()).length();
        AtomicInteger atomicIntegerId = new AtomicInteger(list.size());
        String numberDate = DateUtils.format(new Date(), DateUtils.NUMBERDATEFORMAT);
        list.forEach(e -> {
            int andDecrement = atomicIntegerId.getAndDecrement();
            String id = numberDate + String.format("%0" + idlen + "d", andDecrement);
            String format = String.format(template, id, e.get(0), retryTime[0]);
            sqlList.add(format);
            if (sqlList.size() == 500) {
                ExportUtil.writeDataToFile(sqlList, "hsCode新增脚本信息" + i);
                sqlList.clear();
                i.getAndIncrement();
                try {
                    TimeUnit.SECONDS.sleep(1);
                } catch (InterruptedException interruptedException) {
                    interruptedException.printStackTrace();
                }
                retryTime[0] = DateUtils.format(new Date(), DateUtils.DEFAULT_DATETIME_FORMAT);
                System.out.println("完成" + i);
            }
        });
        if (sqlList.size() > 0) {
            ExportUtil.writeDataToFile(sqlList, "hsCode新增脚本信息最后的" + sheet);
            System.out.println("完成");
        }
    }


    @Test
    public void generateTsSQLPrint() {
        String filePath = "C:\\Users\\181596\\Desktop\\大包下推操作.xlsx";
        int sheet = 0;
//        String template = "INSERT INTO t_base_dictionary(id, parent_code, value_code, value_name, value_seq, language_code, memo, version_no, is_active, create_time, create_user_code, modify_time, modify_user_code) " +
//                "VALUES ('%s', 'invoice_type_code', '%s', '%s', '%s', 'zh-CN', null, 1725242104686, 1, now(), '181596', now(), '181596');";
//        String template = "INSERT INTO t_epass_item_clear_config (id, clear_platform, project, origin, dest, code_msg, is_active, create_user_code, create_time, modify_user_code, modify_time)\n" +
//                "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', 1, '181596', now(), '181596', now());";
        String template = "INSERT INTO `t_waybill_confirm_shipment_error` (`id`, `tracking_number`, `send_leg`, `vendor_code`, `error_reason`, `retry_status`, `retry_times`, `retry_time`, `notify_status`, `notify_fail_reason`, `notify_fail_times`, `is_active`, `create_time`, `create_user_code`, `modify_time`, `modify_user_code`, `api_code`, `jv_event_code`, `request_body`)\n" +
                "VALUES ('%s', '%s', '%s', '%s', '重推', 0,0,now(),0,NULL,NULL, 1, now(), '181596', now(), '181596',  '%s', 'HMSRS-SORTSZX', null);";
        List<List<Object>> list = ImportUtil.readExcelToList(filePath, sheet);
        list.forEach(e -> {
            try {
                Thread.sleep(100);
            } catch (Exception exception) {
                exception.printStackTrace();
            }
            String id = System.currentTimeMillis() + "";
//            String str = (String) e.get(0);
//            String[] split = str.split("=");
            String format = String.format(template, id, e.get(0), e.get(1), e.get(2), e.get(3));
            System.out.println(format);
        });
    }

    //写一个四位随机数
    private Integer getRandomNum() {
        Random random = new Random();
        // 生成一个 1000 到 9999 之间的随机整数
        int randomNum = random.nextInt(9000) + 1000;
        return randomNum;
    }

    /**
     *  生成大单号
     */
    @Test
    public void  generateBigNoSql() {
        //String filePath = "C:\\Users\\181596\\Desktop\\UBI-CE XL\\OC ITALY CE大包号段.xlsx";
        String filePath = "C:\\Users\\181596\\Desktop\\UBI-CE XL\\OC ITALY XL大包号段.xlsx";
        String fileName = filePath.substring(filePath.lastIndexOf("\\") + 1, filePath.lastIndexOf("."));
        int sheet = 0;
        String template = "INSERT INTO t_base_serial_no(serial_no,serial_name) VALUES ('%s', '%s');";

        List<List<Object>> list = ImportUtil.readExcelToList(filePath, sheet);
        List<Object> sqlList = new LinkedList<>();

        list.forEach(e -> {
            String serialNo = String.valueOf(e.get(0)).replaceAll("\\s+", "");
            String serialName = String.valueOf(e.get(1)).replaceAll("\\s+", "");
            String format = String.format(template, serialNo, serialName);
            sqlList.add(format);
        });

        ExportUtil.writeDataToFile(sqlList, fileName + sheet);
        System.out.println("完成");
    }

}
